package com.carmaintain.db;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;

import com.carmaintain.common.Constants;
import com.carmaintain.common.Utils;
import com.carmaintain.config.Configs;
import com.carmaintain.models.Area;
import com.carmaintain.models.ItemBrand;
import com.carmaintain.models.ItemType;
import com.carmaintain.models.MaintainItem;
import com.carmaintain.models.MerchantPlace;
import com.carmaintain.models.MerchantType;

public class CommonLib {
	/**
	 * 取得商家列表
	 * 
	 * @param	page	当前页
	 * @param	pageSize	每页显示数
	 */
	public static Map<String, Object> getMerchantList(int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<MerchantPlace> ret = null;
		MerchantPlace place = null;
		ResultSet result;
		String limitStr = "";
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		String sql = "" +
				" 	SELECT " +
				"		A.id, place_name, address, road_nearby, gps_location, linkman, office_phone, mobile, business_item, B.image_name " +
				" 	FROM " + Constants.TABLE_MERCHANT_PLACE + " A " +
				"		LEFT JOIN " + Constants.TABLE_PLACE_PHOTO + " B " +
				"		ON A.id = B.place_id " +
				"	WHERE status = " + Constants.PLACE_STATUS_VERIFIED +
				limitStr;
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<MerchantPlace>();
			do {
				place = new MerchantPlace();
				place.setId(result.getString("id"));
				place.setPlaceName(result.getString("place_name"));
				place.setAddress(result.getString("address"));
				place.setNearby(result.getString("road_nearby"));
				place.setGpsLocation(result.getString("gps_location"));
				place.setLinkMan(result.getString("linkman"));
				place.setOfficePhone(result.getString("office_phone"));
				place.setMobile(result.getString("mobile"));
				place.setBusinessItem(result.getString("business_item"));
				place.setStatus(Constants.PLACE_STATUS_VERIFIED + "");
				place.setType(Constants.PLACE_TYPE_NORMAL + "");
				
				List<String> photos = new ArrayList<String>();
				photos.add(Configs.uploadPath.replaceAll("\\\\", "/") + result.getString("image_name"));
				place.setPlacePhoto(photos);
				
				ret.add(place);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		sql = "" +
				" 	SELECT " +
				"		count(1) as total_num " +
				" 	FROM " + Constants.TABLE_MERCHANT_PLACE +
				"	WHERE status = " + Constants.PLACE_STATUS_VERIFIED;
		
		result = DBConn.query(sql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		
		return dataMap;
	}
	
	/**
	 * 搜索经营地址
	 * 
	 * @param	keyword	搜索关键字
	 * @param	page	当前页
	 * @param	pageSize	每页显示数
	 */
	public static Map<String, Object> searchPlaces(String placeType, String keyword, float lat, float lng, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<MerchantPlace> ret = null;
		MerchantPlace place = null;
		ResultSet result;
		String whereStr = "";
		String limitStr = "";
		
		if (!Utils.empty(placeType)) {
			whereStr += "	AND place_type = '" + StringEscapeUtils.escapeSql(placeType) + "' ";
		}
		
		if (lat != 0 || lng != 0) {
			whereStr += "	AND ABS(SUBSTRING_INDEX(gps_location, ',', 1) - " + lat  + ") < 0.02 ";
			whereStr += "	AND ABS(SUBSTRING_INDEX(gps_location, ',', -1) - " + lng  + ") < 0.02 ";
		}
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		String sql = "" +
				" 	SELECT " +
				"		A.id, place_name, address, road_nearby, gps_location, linkman, office_phone, mobile, " +
				"		business_item, B.image_name, C.total_num, C.general_satisfaction, C.good_num, C.normal_num, C.bad_num " +
				" 	FROM " + Constants.TABLE_MERCHANT_PLACE + " A " +
				"		LEFT JOIN " + Constants.TABLE_PLACE_PHOTO + " B " +
				"		ON A.id = B.place_id " +
				"		LEFT JOIN " + Constants.VIEW_PLACE_COMMENT_SINGLE + " C " +
				"		ON A.id = C.place_id " +
				"	WHERE status = " + Constants.PLACE_STATUS_VERIFIED +
				"	AND ( place_name like '%" + StringEscapeUtils.escapeSql(keyword) + "%'" +
				"		OR address like '%" + StringEscapeUtils.escapeSql(keyword) + "%')" +
				whereStr +
				"	GROUP BY A.id " +
				limitStr;
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<MerchantPlace>();
			do {
				place = new MerchantPlace();
				place.setId(result.getString("id"));
				place.setPlaceName(result.getString("place_name"));
				place.setAddress(result.getString("address"));
				place.setNearby(result.getString("road_nearby"));
				place.setGpsLocation(result.getString("gps_location"));
				place.setLinkMan(result.getString("linkman"));
				place.setOfficePhone(result.getString("office_phone"));
				place.setMobile(result.getString("mobile"));
				place.setBusinessItem(result.getString("business_item"));
				place.setStatus(Constants.PLACE_STATUS_VERIFIED + "");
				place.setType(Constants.PLACE_TYPE_NORMAL + "");
				
				List<String> photos = new ArrayList<String>();
				photos.add(Configs.uploadPath.replaceAll("\\\\", "/") + result.getString("image_name"));
				place.setPlacePhoto(photos);

				place.setCommentCount(result.getInt("total_num"));
				place.setGeneralSatisfaction(result.getInt("general_satisfaction"));
				place.setGoodNum(result.getFloat("good_num"));
				place.setNormalNum(result.getFloat("normal_num"));
				place.setBadNum(result.getFloat("bad_num"));
				
				ret.add(place);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		sql = "" +
				" 	SELECT " +
				"		count(1) as total_num " +
				" 	FROM " + Constants.TABLE_MERCHANT_PLACE +
				"	WHERE status = " + Constants.PLACE_STATUS_VERIFIED +
				"	AND ( place_name like '%" + StringEscapeUtils.escapeSql(keyword) + "%'" +
				"		OR address like '%" + StringEscapeUtils.escapeSql(keyword) + "%')" +
				whereStr;
		
		result = DBConn.query(sql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		
		return dataMap;
	}

	/**
	 * 取得经营场所详情
	 */
	public static MerchantPlace getPlaceDetail(String placeId) throws Exception{
		MerchantPlace place = null;
		ResultSet result;
		String sql = "";
		
		sql = "" +
				" 	SELECT " +
				"		A.id, place_name, address, road_nearby, gps_location, linkman, A.office_phone, A.mobile, " +
				"		business_item, B.introduction, C.total_num, C.general_satisfaction, C.good_num, C.normal_num, C.bad_num " +
				" 	FROM " + Constants.TABLE_MERCHANT_PLACE + " A " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT + " B " +
				"		ON A.merchant_id = B.user_id " +
				"		LEFT JOIN " + Constants.VIEW_PLACE_COMMENT_SINGLE + " C " +
				"		ON A.id = C.place_id " +
				" 	WHERE A.id = '" + StringEscapeUtils.escapeSql(placeId) + "'" +
				"	AND status = " + Constants.PLACE_STATUS_VERIFIED;
		result = DBConn.query(sql);
		
		if (result.first()) {
			place = new MerchantPlace();
			place.setId(result.getString("id"));
			place.setPlaceName(result.getString("place_name"));
			place.setAddress(result.getString("address"));
			place.setNearby(result.getString("road_nearby"));
			place.setGpsLocation(result.getString("gps_location"));
			place.setLinkMan(result.getString("linkman"));
			place.setOfficePhone(result.getString("office_phone"));
			place.setMobile(result.getString("mobile"));
			place.setBusinessItem(result.getString("business_item"));
			place.setStatus(Constants.PLACE_STATUS_VERIFIED + "");
			place.setType(Constants.PLACE_TYPE_NORMAL + "");
			place.setCommentCount(result.getInt("total_num"));
			place.setGeneralSatisfaction(result.getInt("general_satisfaction"));
			place.setGoodNum(result.getFloat("good_num"));
			place.setNormalNum(result.getFloat("normal_num"));
			place.setBadNum(result.getFloat("bad_num"));
			place.setMerchantIntroduction(result.getString("introduction"));
			
			//取得所有场所照片
			sql = "" +
					" SELECT image_name " +
					" FROM " + Constants.TABLE_PLACE_PHOTO +
					" WHERE place_id = '" + StringEscapeUtils.escapeSql(placeId) + "'";
			result = DBConn.query(sql);
			if (result.first()) {
				List<String> photos = new ArrayList<String>();
				do {
					photos.add(Configs.uploadPath.replaceAll("\\\\", "/") + result.getString("image_name"));
				} while(result.next());
				place.setPlacePhoto(photos);
			}
		}
		
		return place;
	}

	/**
	 * 获取当前地域
	 * @param int areaId
	 * @return List<Area>
	 */
	public static Area getCurrentAreaById(int areaId) throws Exception {
		ResultSet result = null;
		Area area = null;
		String sql="" +
				" SELECT " +
				" 	region_id, " +
				" 	region_name, " +
				" 	parent_id " +
				" FROM " + 
					Constants.TABLE_REGION +
				" WHERE " +
				"	region_id = " + areaId;
			result = DBConn.query(sql);
			if (result.first()) {
					area = new Area();
					area.setRegionId(result.getInt("region_id"));
					area.setRegionName(result.getString("region_name"));
					area.setParentId(result.getInt("parent_id"));
					area.setChildArea(getChildAreaById(areaId));
			}
		return area;
	}
	
	/**
	 * 获取当前地域直接子地域
	 * @param int areaId
	 * @return List<Area>
	 */
	public static List<Area> getChildAreaById(int areaId) throws Exception {
		ResultSet result = null;
		List<Area> areaList=null;
		String sql="" +
				" SELECT " +
				" 	region_id, " +
				" 	region_name, " +
				" 	parent_id " +
				" FROM " + 
					Constants.TABLE_REGION +
				" WHERE " +
				"	parent_id = " + areaId;
			result = DBConn.query(sql);
			if (result.first()) {
				areaList=new ArrayList<Area>();
				Area area;
				do {
					area=new Area();
					area.setRegionId(result.getInt("region_id"));
					area.setRegionName(result.getString("region_name"));
					area.setParentId(result.getInt("parent_id"));
					areaList.add(area);
				} while (result.next());
			}
		return areaList;
	}
	
	/**
	 * 获取指定父分类的所有子分类
	 * @param String parentId
	 * @return List<ItemType>
	 */
	public static List<ItemType> getItemType(String parentId) throws Exception {
		ResultSet result = null;
		List<ItemType> typeList=null;
		ItemType type;
		
		String sql="" +
				" SELECT " +
				" 	id, " +
				" 	type_name, " +
				" 	is_leaf " +
				" FROM " + 
					Constants.TABLE_ITEM_TYPE +
				" WHERE " +
				"	parent_id = " + StringEscapeUtils.escapeSql(parentId);
		result = DBConn.query(sql);
		if (result.first()) {
			typeList = new ArrayList<ItemType>();
			do {
				type = new ItemType();
				
				type.setTypeId(result.getString("id"));
				type.setTypeName(result.getString("type_name"));
				type.setIsLeaf(result.getInt("is_leaf") == 1);
				
				typeList.add(type);
			} while (result.next());
		}
		return typeList;
	}
	
	/**
	 * 指定分类的品牌列表
	 * @param String parentId
	 * @return List<ItemType>
	 */
	public static List<ItemBrand> getItemBrand(String typeId) throws Exception {
		ResultSet result = null;
		List<ItemBrand> brandList=null;
		ItemBrand brand;
		String whereStr = "";
		
		if (!Utils.empty(typeId)) {
			whereStr = " WHERE A.type_id = '" + StringEscapeUtils.escapeSql(typeId) + "'";
		}
		
		String sql="" +
				" SELECT " +
				" 	A.brand_id, " +
				" 	B.brand_name " +
				" FROM " + Constants.TABLE_ITEM + " A " +
				" 	LEFT JOIN " +Constants.TABLE_ITEM_BRAND + " B " +
				" 	ON A.brand_id = B.id " +
				whereStr +
				" GROUP BY A.brand_id ";
		result = DBConn.query(sql);
		if (result.first()) {
			brandList = new ArrayList<ItemBrand>();
			do {
				brand = new ItemBrand();
				
				brand.setBrandId(result.getString("brand_id"));
				brand.setBrandName(result.getString("brand_name"));
				
				brandList.add(brand);
			} while (result.next());
		}
		return brandList;
	}
	
	/**
	 * 指定分类及品牌的养护项目列表
	 * @param String parentId
	 * @return List<ItemType>
	 */
	public static List<MaintainItem> getMaintainItem(String typeId, String brandId) throws Exception {
		ResultSet result = null;
		List<MaintainItem> itemList=null;
		MaintainItem item;
		String whereStr = "";
		
		if (!Utils.empty(typeId)) {
			whereStr = " WHERE type_id = '" + StringEscapeUtils.escapeSql(typeId) + "'";
		}
		if (!Utils.empty(brandId)) {
			whereStr += Utils.empty(whereStr) ? " WHERE " : " AND ";
			whereStr += " brand_id = '" + StringEscapeUtils.escapeSql(brandId) + "'";
		}
		
		String sql="" +
				" SELECT " +
				" 	A.id, " +
				" 	A.model, " +
				" 	B.type_name, " +
				" 	C.brand_name, " +
				" 	D.period_name " +
				" FROM " + Constants.TABLE_ITEM + " A " +
				"	LEFT JOIN " + Constants.TABLE_ITEM_TYPE + " B " +
				"	ON A.type_id = B.id " +
				"	LEFT JOIN " + Constants.TABLE_ITEM_BRAND + " C " +
				"	ON A.brand_id = C.id " +
				"	LEFT JOIN " + Constants.TABLE_ITEM_PERIOD + " D " +
				"	ON A.period_id = D.id " +
				whereStr;
		result = DBConn.query(sql);
		if (result.first()) {
			itemList = new ArrayList<MaintainItem>();
			do {
				item = new MaintainItem();
				
				item.setItemId(result.getString("id"));
				item.setItemModel(result.getString("model"));
				item.setItemTypeName(result.getString("type_name"));
				item.setItemBrandName(result.getString("brand_name"));
				item.setItemPeriodName(result.getString("period_name"));
				
				itemList.add(item);
			} while (result.next());
		}
		return itemList;
	}
	
	/**
	 * 取得商家种类列表
	 */
	public static List<MerchantType> getMerchantTypes() throws Exception{
		ResultSet result = null;
		List<MerchantType> types = null;
		MerchantType type = null;
		
		String sql = "" +
				" 	SELECT " +
				"		id, type_name " +
				" 	FROM " + Constants.TABLE_PLACE_TYPE +
				"	ORDER BY id ASC ";
		result = DBConn.query(sql);
		
		if (result.first()) {
			types = new ArrayList<MerchantType>();
			do {
				type = new MerchantType();
				type.setTypeId(result.getString("id"));
				type.setTypeName(result.getString("type_name"));
				
				types.add(type);
			} while (result.next());
		}
		
		return types;
	}
	
	/**
	 * 检测新版本
	 * 
	 * @param	currentVersion	当前版本号
	 */
	public static Map<String, Object> checkApkVersion(int currentVersion) throws Exception{
		Map<String, Object> dataMap = null;
		ResultSet result;
		
		String sql = "" +
				" 	SELECT " +
				"		version, version_name, down_url " +
				" 	FROM " + Constants.TABLE_APK_VERSION +
				"	WHERE version > " + currentVersion;

		result = DBConn.query(sql);
		
		if (result.first()) {
			dataMap = new HashMap<String, Object>();
			dataMap.put("versionCode", result.getString("version"));
			dataMap.put("versionName", result.getString("version_name"));
			dataMap.put("downUrl", result.getString("down_url"));
		}
		
		return dataMap;
	}
}
